package dao.dao;

import dao.bean.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.ProxoolDriver;

public class UserDaoJdbcProxool implements UserDao {
    static {
        try {
            Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("There is no Proxool driver in classpath", e);
        }
        Properties info = new Properties();
        info.setProperty("proxool.maximum-connection-count", "10");
        info.setProperty("proxool.house-keeping-test-sql", "select CURRENT_DATE");
        info.setProperty("user", "root");
        info.setProperty("password", "2479");
        String alias = "production";
        String driverClass = "com.mysql.jdbc.Driver";
        String driverUrl = "jdbc:mysql://localhost:3306/production_eshop";
        String url = "proxool." + alias + ":" + driverClass + ":" + driverUrl;
        try {
            ProxoolFacade.registerConnectionPool(url, info);
        } catch (ProxoolException e) {
            throw new RuntimeException("Some error configuring Proxool", e);
        }
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("proxool.production");
    }

    public List<User> selectAll0() throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement("SELECT id, login FROM User");
            rs = ps.executeQuery();
            ArrayList<User> result = new ArrayList<User>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String login = rs.getString("login");
                User user = new User(id);
                user.setLogin(login);
                result.add(user);
            }
            return result;
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public List<User> selectAll() throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("SELECT id, login FROM User");
            rs = ps.executeQuery();
            ArrayList<User> result = new ArrayList<User>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String login = rs.getString("login");
                User user = new User(id);
                user.setLogin(login);
                result.add(user);
            }
            conn.commit();
            return result;
        } catch (SQLException e) {
            JdbcUtils.rollbackQuietly(conn);
            throw e;
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public int deleteById(int id) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("DELETE FROM User WHERE id = ?");
            ps.setInt(1, id);
            return ps.executeUpdate();
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public void insert(User user) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("INSERT INTO User (login) VALUES (?)");
            ps.setString(1, user.getLogin());
            ps.executeUpdate();
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public void shutdown() {
        // NOP
    }    
    public static void main(String[] args) throws SQLException {
        UserDaoJdbcProxool uPr = new UserDaoJdbcProxool();
        System.out.println(uPr.selectAll());
    }
}

